<?php
/**
 * Created by PhpStorm.
 * User: Jython <ijython@qq.com>
 * Date: 2017/8/11
 * Time: 0:35
 */
//error_reporting( -1 );


class DB
{
    private $dbname = null;
    private $_sql_keys = array();
    private $args = array();
    private $simple = false;//是否开启sql语句简略模式,开启后只需要在select上写表名,后面where group having order 都怎么会加入表别名
    static private $table_prefixs = array();
    static private $_tx = false;
    static private $conns = array();
    static private $conn = null;
    static $error = null;//执行时出的错误信息
    static $lastsql = null; //可以通过$GLOBALS[ 'lastsql' ]来获取
    static $db_config_path = 'db.ini';//数据库配置文件路径
    static $lastInsertId = null;

    function __construct( $dbname, $simple )
    {
        $this->simple = $simple;
        $this->dbname = $dbname;
        $this->conn( $dbname );
    }

    private function conn( $dbname )
    {
        try {
            if ( empty( self::$conns[ $dbname ] ) ) {
                $db_config = parse_ini_file( self::$db_config_path, true );
                $config = @$db_config[ $dbname ];
                self::$table_prefixs[ $dbname ] = @$config[ 'table_prefix' ];
                $options = array(
                    PDO::ATTR_TIMEOUT            => 3,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                    PDO::ATTR_STRINGIFY_FETCHES  => false,
                    PDO::ATTR_PERSISTENT         => false
                );
                $db = new PDO( 'mysql:host=' . $config[ 'host' ] . ':' . $config[ 'port' ] . ';dbname=' . $config[ 'dbname' ], $config[ 'username' ], $config[ 'password' ], $options );
                self::$conns[ $dbname ] = self::$conn = $db;
            }
        } catch ( PDOException $e ) {
            self::$error = $e->getmessage();
            header( "Content-type: text/html; charset=utf-8" );
            exit( "\r\n<br/>" . self::$error );
            exit( "SQLSTATE[HY000] [1045] Access denied for mysql,please check username or password for [$dbname]" );
        }
    }

    private function getKey( $key )
    {
        if ( array_key_exists( $key, $this->_sql_keys ) ) {
            return $this->_sql_keys[ $key ];
        }

        return '';
    }

    private function setKey( $key, $val )
    {
        $this->_sql_keys[ $key ] = $val;

        return true;
    }

    /**开启事务
     * @return $this
     */
    function Begin()
    {
        self::$_tx = true;

        return $this;
    }

    /**结束事务
     * @param bool $isok
     * @return $this
     */
    function End( $isok = true )
    {
        if ( $isok ) {
            self::$conn->commit();
        } else {
            self::$conn->rollback();
        }
        self::$_tx = false;

        return $this;
    }

    function Select( $columns )
    {
        if ( $columns == "" ) {
            $columns = '*';
        }
        $this->_sql_keys[ 'select' ] = 'select ' . $columns;

        return $this;
    }

    function From( $table )
    {
        $this->setKey( 'from', 'from ' . self::$table_prefixs[ $this->dbname ] . $table );

        return $this;
    }

    function Join( $table, $on )
    {
        $table = self::$table_prefixs[ $this->dbname ] . $table;
        if ( $on == '' ) {
            return $this;
        }
        if ( strpos( $on, '=' ) !== false ) {
            $on_arr = explode( '=', $on );

            if ( strpos( $on_arr[ 0 ], '.' ) === false ) {
                $previous_table = $this->getKey( 'join' );
                if ( $previous_table ) {
                    $previous_table = end( $previous_table );
                } else {
                    $previous_table = $this->getKey( 'from' );
                }
                $on_arr[ 0 ] = @end( explode( ' ', $previous_table ) ) . '.' . $on_arr[ 0 ];
            }
            if ( strpos( $on_arr[ 1 ], '.' ) === false ) {
                $on_arr[ 1 ] = @end( explode( ' ', $table ) ) . '.' . $on_arr[ 1 ];
            }

            $on = join( '=', $on_arr );
            $on = 'on ' . $on;
        } else {
            $on = 'using (' . $on . ')';
        }
        $this->_sql_keys[ 'on' ][ ] = $on;
        $this->_sql_keys[ 'join' ][ ] = 'left join ' . $table;

        return $this;
    }

    /**
     * @param                  $where
     * @param int|string|array $params
     * @return $this
     */
    function Where( $where, $params )
    {
        self::setKey( 'where', 'where ' . $where );
        if ( is_array( $params ) ) {
            $this->args = array_merge( $this->args, $params );
        } else {
            array_push( $this->args, $params );
        }

        return $this;
    }

    /**
     * @param string $group
     * @return $this
     */
    function Group( $group )
    {
        if ( $group ) {
            $this->_sql_keys[ 'group' ] = 'group by ' . $group;
        }

        return $this;
    }

    /**
     * @param string $having
     * @return $this
     */
    function Having( $having )
    {
        if ( $having ) {
            $this->_sql_keys[ 'having' ] = 'having ' . $having;
        }

        return $this;
    }

    /**
     * @param string $order
     * @return $this
     */
    function Order( $order )
    {
        if ( $order ) {
            $this->_sql_keys[ 'order' ] = 'order by ' . $order;
        }

        return $this;
    }

    /**
     * @param int $start
     * @param int $offset
     * @return $this
     */
    function Limit( $start, $offset = 0 )
    {
        if ( $offset ) {
            $this->_sql_keys[ 'limit' ] = 'limit ' . $start . ',' . $offset;
        } else {
            $this->_sql_keys[ 'limit' ] = 'limit ' . $start;
        }

        return $this;
    }

    function Table( $table )
    {
        $this->_sql_keys[ 'table' ] = self::$table_prefixs[ $this->dbname ] . $table;

        return $this;
    }

    function Update( array $data )
    {
        $table = self::getKey( 'table' );
        $fileds = array();
        $values = array();
        foreach ( $data as $k => &$v ) {
            $fileds[ ] = "`$k`=?";
            $values[ ] = $v;
        }
        $this->args = array_merge( $values, $this->args );
        $sql = sprintf( 'update %s set %s %s', $table, join( ',', $fileds ), self::getKey( 'where' ) );

        return $this->Query( $sql, $this->args );
    }

    function Insert( array $data )
    {
        return $this->_save( $data );
    }

    function Replace( array $data )
    {
        return $this->_save( $data, 'replace' );
    }

    private function _save( array $data, $type = 'insert' )
    {
        $table = $this->_sql_keys[ 'table' ];
        $fileds = '`' . join( '`,`', array_keys( $data ) ) . '`';
        $values = array();
        $this->args = array();
        foreach ( $data as $k => &$v ) {
            array_push( $this->args, $v );
            $values[ ] = "?";
        }
        $sql = sprintf( '%s into %s (%s) values (%s)', $type, $table, $fileds, join( ',', $values ) );

        return $this->Query( $sql, $this->args );
    }

    function Inserts( array $datas )
    {
        return $this->_saves( $datas, 'insert' );
    }

    function Replaces( array $datas )
    {
        return $this->_saves( $datas, 'replace' );
    }

    private function _saves( array $data, $type = 'insert' )
    {
        $table = $this->_sql_keys[ 'table' ];
        $fileds = '`' . join( '`,`', array_keys( (array)@$data[ 0 ] ) ) . '`';
        $values = array();
        $this->args = array();
        foreach ( $data as $k => &$v ) {
            $_temp = array();
            foreach ( $v as $p => &$q ) {
                array_push( $this->args, $q );
                $_temp[ ] = "?";
            }
            $_temp[ 0 ] = '(' . $_temp[ 0 ];
            $_temp[ ] = array_pop( $_temp ) . ')';
            $values[ ] = join( ',', $_temp );
        }
        $sql = sprintf( '%s into %s (%s) values %s', $type, $table, $fileds, join( ',', $values ) );

        return $this->Query( $sql, $this->args );
    }

    function Delete()
    {
        $table = self::getKey( 'table' );
        $where = self::getKey( 'where' );
        if ( !$where ) {
            return false;
        }
        $sql = sprintf( 'delete %s %s', $table, $where );
        $GLOBALS[ 'lastsql' ] = self::$lastsql = vsprintf( str_replace( '?', "'%s'", $sql ), $this->args );

        return $this->Query( $sql, $this->args );
    }

    /**可以写原生sql语句
     * @param      $sql
     * @param null $params
     * @return bool|null
     */
    function Query( $sql, $params = null )
    {
        $sql = trim( $sql );
        if ( !$params ) {
            $this->args = $params = array_merge( $this->args, $params );
        }
        $conn = self::$conns[ $this->dbname ];
        if ( self::$_tx ) {
            $conn->beginTransaction();
        }
        $res = null;
        try {
            $stmt = $conn->prepare( $sql );
            $res = $stmt->execute( $this->args );
            if ( preg_match( '/^select/', $sql ) ) {
                $res = $stmt->fetchAll( PDO::FETCH_ASSOC );
            } elseif ( preg_match( '/^insert/', $sql ) ) {
                $res = $stmt->rowCount();
                self::$lastInsertId = $conn->lastInsertId();
            }
        } catch ( PDOException $e ) {
            self::$error = $e->getMessage();
        }
        $GLOBALS[ 'lastsql' ] = self::$lastsql = str_replace( '   ', ' ', vsprintf( str_replace( '?', "'%s'", $sql ), $params ) );
        $this->_after();
        if ( self::$error ) {
            return false;
        }

        return $res;
    }

    function GetAll()
    {
        $sql = $this::_makeSql();

        return $this->Query( $sql, $this->args );
    }

    function GetRow()
    {
        $this->Limit( 1 );
        $sql = $this::_makeSql();
        $res=$this->Query( $sql, $this->args );
        if($res){
            return $res[0];
        }
        return array();
    }

    function getLastSql()
    {
        return $GLOBALS[ 'lastsql' ];
    }

    private function _makeSql()
    {
        $join = $this->getKey( 'join' );
        if ( $join ) {
            $on = $this->getKey( 'on' );
            $temp_join = '';
            $join_count = count( $join );
            for ( $i = 0; $i < $join_count; $i++ ) {
                $temp_join .= $join[ $i ] . ' ' . $on[ $i ] . ' ';
            }
            $join = $temp_join;
        }
        $select = $this->getKey( 'select' );
        preg_match_all( '/(\w+)\.(\w+)/i', $select, $matches );
        if ( $this->simple && 0<$count = count( $matches[ 0 ] ) ) {
            $sql = sprintf( " %s %s %s %s %s %s %s", self::getKey( 'from' ), $join, self::getKey( 'where' ), self::getKey( 'group' ), self::getKey( 'having' ), self::getKey( 'order' ), self::getKey( 'limit' ) );
            for ( $i = 0; $i < $count; $i++ ) {
                $sql = str_replace( ' ' . $matches[ 2 ][ $i ], ' ' . $matches[ 1 ][ $i ].'.'.$matches[ 2 ][ $i ], $sql );
            }
            $sql = self::getKey( 'select' ) . $sql;
        } else {
            $sql = sprintf( "%s %s %s %s %s %s %s %s", self::getKey( 'select' ), self::getKey( 'from' ), $join, self::getKey( 'where' ), self::getKey( 'group' ), self::getKey( 'having' ), self::getKey( 'order' ), self::getKey( 'limit' ) );
        }
        return $sql;
    }

    function getLastInertId()
    {
        return self::$lastInsertId;
    }

    private function _after()
    {
        if ( self::$error ) {
            echo self::$error;
        }
    }
}

function M( $dbname, $simple = false )
{
    return new DB( $dbname, $simple );
}
